<?php
/**
 * Created by PhpStorm.
 * User: Shen
 * Date: 2016/6/28
 * Time: 22:09
 */
header("Content-type:text/html;charset=utf-8");

$mysqli = new mysqli('localhost','root','123456','test');

if ($mysqli->connect_errno){
    die('连接错误：' . $mysqli->connect_error);
}

$mysqli->set_charset('utf8');


$sql = "INSERT user(name, age, class) VALUES('king', '25', '高三五班');";
$sql .= "UPDATE user SET age=100 WHERE id=4;";
$sql .= "DELETE FROM user WHERE id=5;";
//针对多条SQL语句的执行
//$res=$mysqli->multi_query($sql); //只看第一条SQL结果，成功了就返回真，不管后面的执行结果，如果第一条失败了，后面的真也执行不到了

//不过一般我们用这个是为了查询
$sql = "SELECT id,name,age FROM user;";
$sql .= "SELECT * FROM mysql.user;";
$sql .= "SELECT CURRENT_USER();";
$sql .= "SELECT NOW();";

//user_result()/store_result() 将结果集中的第一个结果取出，后者效率要高
//more_results() : 检测是否有更多的结果集
//next_result() : 将结果集指针向下移动一位

if($mysqli->multi_query($sql)){
    do{
        if ($mysqli_result=$mysqli->store_result()){
            $rows[] = $mysqli_result->fetch_assoc();
        }
    }while($mysqli->more_results() && $mysqli->next_result());
}else{
    echo $mysqli->error;
}

print_r($rows);


//使用预处理指令
$sql = "INSERT user(name, age, class) VALUES(?,?,?)";
//准备预处理语句
$mysqli_stmt = $mysqli->prepare($sql);

$name = 'king';
$age = 12;
$class = '高一五班';

//绑定参数
$mysqli_stmt->bind_param('sis',$name,$age,$class);

//执行预处理语句
if($mysqli_stmt->execute()){
    echo $mysqli_stmt->insert_id;
    echo '<br />';
}else{
    $mysqli_stmt->error;
}

$mysqli_stmt->free_result(); //释放
$mysqli_stmt->close();//关闭

//预处理语句能够防止sql注入



//查询中使用预处理指令
$sql="SELECT id,name,age,class FROM user WHERE id=?";
$mysqli_stmt=$mysqli->prepare($sql);
$id=4;
$mysqli_stmt->bind_param('i',$id);
if ($mysqli_stmt->execute()){
    //bind_result()绑定结果集中的值到变量
    $mysqli_stmt->bind_result($id,$name,$age,$class);
    while ($mysqli_stmt->fetch()){
        echo '编号：'. $id .'<br />';
        echo '用户名：'. $name .'<br />';
        echo '年龄：'. $age .'<br />';
        echo '班级：'. $class .'<br />';
        echo '<hr />';
    }
}

$mysqli_stmt->free_result(); //释放
$mysqli_stmt->close();//关闭


//实现事物处理，这里提到的是转账的简单处理
//先关闭自动提交
$mysqli->autocommit(FALSE);
$sql = '第一个操作';
$res=$mysqli->query($sql);
$res_affect=$mysqli->affected_rows;

$sql1 = '第二个操作';
$res1=$mysqli->query($sql1);
$res1_affect=$mysqli->affected_rows;

if ($res && $res_affect > 0 && $res1 && $res1 > 0){
    $mysqli->commit();
    echo '转账成功<br />';
    $mysqli->autocommit(TRUE);
}else{
    $mysqli->rollback(); //事物回滚
    echo '转账失败 <br />';
}



$mysqli->close();

